package org.example.dao;


import org.example.pojo.Role;
import org.example.pojo.User;
import org.example.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDao {

    // 登录，根据用户名和密码查询用户
    public User login(String username, String password) throws SQLException {

        //		获得数据库链接
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT id, username, password FROM user WHERE username=? AND password=?";
        //定义sql查询语句
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, username);
        ps.setString(2, password);
        //执行查询
        ResultSet rs = ps.executeQuery();

        User user = null;
        if (rs.next()) {
            user = new User();
            //设置对象属性
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
        }
        return user;
    }
//注册方法，添加新用户到数据库
    public void register(String username, String password,String email) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into user(username,password,email,create_time,modify_time) values(?,?,?,now(),now())";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, password);
        ps.setString(3, email);
        ps.executeUpdate();
        System.out.println("注册成功");
}

    // 获有用户 , 根据 id
    public User getUser(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT * FROM user where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,id);
        ResultSet rs = ps.executeQuery();
        User user = new User();
        while (rs.next()) {
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setGender(rs.getString("gender"));
            user.setAge(rs.getInt("age"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setCreateTime(rs.getTimestamp("create_time"));
            user.setModifiedTime(rs.getTimestamp("modify_time"));
        }
        return user;
    }

    // 获取用户总数
    public int getTotalUserCount() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT COUNT(*) FROM user";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    // 获取分页用户
    public List<User> getUsers(int currentPage, int pageSize) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        int offset = (currentPage - 1) * pageSize;
        String sql = "SELECT * FROM user LIMIT ? OFFSET ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, pageSize);
        ps.setInt(2, offset);
        ResultSet rs = ps.executeQuery();

        List<User> userList = new ArrayList<>();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setGender(rs.getString("gender"));
            user.setAge(rs.getInt("age"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setCreateTime(rs.getTimestamp("create_time"));
            user.setModifiedTime(rs.getTimestamp("modify_time"));
            userList.add(user);
        }
        return userList;
    }

    // 通过ID获取用户
    public User getUserById(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT id, username, password, gender, age, email, phone, address, create_time, modify_time FROM user WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();

        User user = null;
        if (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setGender(rs.getString("gender"));
            user.setAge(rs.getInt("age"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setCreateTime(rs.getTimestamp("create_time"));
            user.setModifiedTime(rs.getTimestamp("modify_time"));
        }
        return user;
    }

    // 通过用户名获取用户
    public User getUserByUsername(String username) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT id, username, password, gender, age, email, phone, address, create_time, modify_time FROM user WHERE username=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();

        User user = null;
        if (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setGender(rs.getString("gender"));
            user.setAge(rs.getInt("age"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setAddress(rs.getString("address"));
            user.setCreateTime(rs.getTimestamp("create_time"));
            user.setModifiedTime(rs.getTimestamp("update_time"));
        }
        return user;
    }

    // 通过ID删除用户
    public void deleteUserById(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "DELETE FROM user WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    }

    // 用户信息
    public User userInfo(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT * FROM user WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        User user = new User();
        if (rs.next()) {
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setGender(rs.getString("gender"));
            user.setAge(rs.getInt("age"));
            user.setAddress(rs.getString("address"));
            user.setCreateTime(rs.getDate("create_time"));
            user.setModifiedTime(rs.getDate("modify_time"));
        }
        return user;
    }

    // 修改用户名
    public void updateUsername(int id, String username) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "UPDATE user SET username=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 修改密码
    public void updatePassword(int id, String password) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "UPDATE user SET password=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, password);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 修改邮箱
    public void updateEmail(int id, String email) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "UPDATE user SET email=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, email);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 修改手机号
    public void updatePhone(int id, String phone) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "update user set phone=? where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, phone);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 修改性别
    public void updateGender(int id, String gender) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "update user set gender=? where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, gender);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 修改年龄
    public void updateAge(int id, int age) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "UPDATE user SET age=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, age);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 修改地址
    public void updateAddress(int id, String address) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "UPDATE user SET address=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, address);
        ps.setInt(2, id);
        ps.executeUpdate();
        System.out.println("修改成功");
    }

    // 通过id 去获取信息
    public Role getRole(int userId, String userName) throws Exception{

        //		获得数据库链接
        Connection connection = JDBCUtils.getConnection();
//        String sql = "insert into users (username,password) values(?,?)";
        String sql = "select distinct user_id,username from user,publish where publish.user_id=?";
//		使用PreparedStatement执行SQL语句可以防止SQL注入
        PreparedStatement ps = connection.prepareStatement(sql);
        //		设置占位符的参数
        ps.setInt(1, userId);

        ResultSet rs = ps.executeQuery();

        Role role = new Role(rs.getInt(userId), rs.getString(userName));
        return role;
    }

    // 获取用户名
    public String getUserName(int userId) throws Exception{

        //		获得数据库链接
        Connection connection = JDBCUtils.getConnection();
//        String sql = "insert into users (username,password) values(?,?)";
        String sql = "select distinct username from user,publish where user.id=?";
//		使用PreparedStatement执行SQL语句可以防止SQL注入
        PreparedStatement ps = connection.prepareStatement(sql);
        //		设置占位符的参数
        ps.setInt(1, userId);

        ResultSet rs = ps.executeQuery();
//        System.out.println(rs.next()+"1111"+rs.getString(1));
        rs.next();
        return rs.getString("username");
    }

    // 通过 用户名和 邮箱和 获取 密码
    public String getPassword(String username, String email) throws Exception{
        Connection connection = JDBCUtils.getConnection();
        String sql = "select password from user where username=? and email=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, email);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getString("password");
        } else {
            return null;
        }
    }

    // 检查 用户 是否存在
    public User getUser(String username) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select username from user where username=?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, username);

        ResultSet rs = ps.executeQuery();
        User user=new User();
        if (rs.next()) {
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setGender(rs.getString("gender"));
            user.setAge(rs.getInt("age"));
            user.setAddress(rs.getString("address"));
            user.setCreateTime(rs.getDate("create_time"));
            user.setModifiedTime(rs.getDate("modify_time"));
        }
        return user;
    }


}
